package com.czn.datasourcesync.model;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;

/**
 * @author Zenan Chen
 * @date 2023/9/15 10:22
 */
@Component
public class CopyTableTask implements Runnable {

    private String tableName;
    private SourceDataSource sourceDataSource;
    private TargetDataSource targetDataSource;

    private int size;

    public CopyTableTask() {
    }

    public CopyTableTask(String tableName, SourceDataSource sourceDataSource, TargetDataSource targetDataSource, int size) {
        this.tableName = tableName;
        this.sourceDataSource = sourceDataSource;
        this.targetDataSource = targetDataSource;
        this.size = size;
    }

    private static final Logger log = LoggerFactory.getLogger(CopyTableTask.class);

    @Override
    public void run() {
        String sourceURL = sourceDataSource.getUrl();
        String sourceUser = sourceDataSource.getUser();
        String sourcePassword = sourceDataSource.getPassword();

        String targetURL = targetDataSource.getUrl();
        String targetUser = targetDataSource.getUser();
        String targetPassword = targetDataSource.getPassword();
        try (Connection conn1 = DriverManager.getConnection(sourceURL, sourceUser, sourcePassword);
             Connection conn2 = DriverManager.getConnection(targetURL, targetUser, targetPassword);
             Statement stmt1 = conn1.createStatement();
             Statement stmt2 = conn2.createStatement()) {
            log.info("==============================================【" + tableName + "】表开始 =========================================");
            // 获取源数据表的建表语句
            String createTableSQL = "show create table " + tableName;
            log.info("---------------> 在数据源查询【{}】的建表语句：{}", tableName, createTableSQL);
            ResultSet createTblRS = stmt1.executeQuery(createTableSQL);
            while (createTblRS.next()) {
                createTableSQL = createTblRS.getString(2);
                log.info("---------------> 在数据源查询【{}】的建表语句：\n{}", tableName, createTableSQL);
                // 查询目标表是否存在
                ResultSet tableExistRS = conn2.getMetaData().getTables(null, null, tableName, new String[]{"TABLE"});
                boolean exist = tableExistRS.next();
                if (exist) {
                    log.info("---------------> 目标表【{}】存在！", tableName);
                    // 删除目标表
                    String dropTableSQL = "drop table " + tableName;
                    log.info("---------------> 在目标源删除【{}】的建表语句：{}", tableName, dropTableSQL);
                    stmt2.execute(dropTableSQL);
                }
                // 新建目标表
                stmt2.execute(createTableSQL);
                log.info("---------------> 在目标源执行【{}】的建表语句：\n{}", tableName, createTableSQL);
            }

            // 查询源表总记录数
            String queryCountSQL = "select count(1) from " + tableName;
            ResultSet queryCountRS = stmt1.executeQuery(queryCountSQL);
            int rows = 0;
            while (queryCountRS.next()) {
                rows = queryCountRS.getInt(1);
                log.info("---------------> 源表表【{}】的记录数是：", rows);
            }
            ResultSet queryRS = stmt1.executeQuery("SELECT * FROM " + tableName + ";");
            int columnCount = queryRS.getMetaData().getColumnCount();
            // 拼接INSERT语句
            StringBuilder sb = new StringBuilder("INSERT INTO " + tableName + " VALUES(");
            for (int j = 0; j < columnCount; j++) {
                sb.append("?,");
            }
            String insertSQL = sb.substring(0, sb.length() - 1);
            insertSQL += ");";
            log.info("---------------> insertSQL的结果是：{}", insertSQL);

            // 执行INSERT到目标表
            PreparedStatement prepareStatement = conn2.prepareStatement(insertSQL);

            // 分批查询源数据表
            int batch = (rows / size) + 1;
            log.info("---------------> 共：{}条记录，分{}批次进行执行，每批最大是{}", rows, batch, size);
            for (int i = 1; i <= batch; i++) {
                Integer start = (i - 1) * size;
                Integer end = size;
                ResultSet rs = stmt1.executeQuery("SELECT * FROM " + tableName + " limit " + start + "," + end);
                while (rs.next()) {
                    for (int j = 1; j <= columnCount; j++) {
                        prepareStatement.setObject(j, rs.getObject(j));
                    }
                    prepareStatement.addBatch();
                }
                prepareStatement.executeBatch();
            }
            log.info("==============================================【{}】表结束 =========================================", tableName);
        } catch (Exception e) {
            log.error("SQLException: {}", e);
        }
    }
}
